package com.sxfq.loansupermarket.backsystem.modular.report.service.impl;

import com.sxfq.loansupermarket.backsystem.common.persistence.model.Dept;
import com.sxfq.loansupermarket.backsystem.core.shiro.ShiroKit;
import com.sxfq.loansupermarket.backsystem.core.util.StringUtil;
import com.sxfq.loansupermarket.backsystem.modular.base.SqlMapper;
import com.sxfq.loansupermarket.backsystem.modular.report.entity.SearchWholeHospitalInfo;
import com.sxfq.loansupermarket.backsystem.modular.report.service.IGetTotleDoctorService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;

@Service
@Transactional
public class GetTotleDoctorServiceImpl implements IGetTotleDoctorService {
    @Autowired
    private SqlMapper sqlMapper;

    public String getsqlDepartment(SearchWholeHospitalInfo searchInfo, String classification, String condition, int i, List<SearchWholeHospitalInfo> list1) {
        String getabbreviationsql = " select id,abbreviation,simplename from dept t where t.simplename='" + searchInfo.getArrivalStore() + "'";
        Dept dept = sqlMapper.selectOne(getabbreviationsql, Dept.class);
        String tableHead = "";
        if (dept == null) {
            tableHead = ShiroKit.getUser().getAbbreviation();
        } else {
            tableHead = dept.getAbbreviation();
        }
        String tableName1 = tableHead + "deal_info";
        String tableName2 = tableHead + "customer_info";
        String tableName3 = tableHead + "recharge_info";
        String sqlb = " ";
        String performance = "hospital_performance";
        if (StringUtil.isNotEmpty(searchInfo.getDimensionSelectionCustomerAll())) {
            if ("ks".equals(searchInfo.getDimensionSelectionCustomerAll())) {
                sqlb += "";
            } else {
                sqlb += " and b.treatment_unit='" + searchInfo.getDimensionSelectionCustomerAll() + "'";
            }
            performance = " section_performance";
        }
        String sql = " select '" + classification + "' as rirstClassification, " +

                " (select count(1) from(select b.id from " + tableName2 + " b  left join " + tableName1 + " a on b.id=a.customer_id where " +
                " b.customer_status='新客首次' and " +
                " b.arrival_time>='" + searchInfo.getStartTime() + "' and b.arrival_time<='" + searchInfo.getEndTime() + "' and " + condition + sqlb + " " +
                " group by b.reservations_id) t) AS outpatientVolume," +

                " (select count(1) from (select b.id from " + tableName2 + " b left join "+dept.getAbbreviation()+"deal_info a on b.id=a.customer_id where b.customer_status='新客首次' " +
                " and b.isdeal_status='已成交' and " +
                " b.arrival_time>='" + searchInfo.getStartTime() + "' and b.arrival_time<='" + searchInfo.getEndTime() + "' and " + condition+ sqlb +
                " group by b.reservations_id) t) AS volume, " +

                " ifnull((select sum(t." + performance + ") from( " +
                " select c." + performance + " from " + tableName3 + " c  " +
                " left join " + tableName1 + " a on c.deal_id=a.id " +
                " left join " + tableName2 + " b on b.id=c.customer_id " +
                " where c.recharge_time >= '" + searchInfo.getStartTime() + "' and c.recharge_time <= '" + searchInfo.getEndTime() + "'" +
                " and " + condition + sqlb + " and b.customer_status='新客首次' group by c.id) t),0.00)  as " +
                " departmentPerformance, '0.00' as tcPerformance from dual " +

                " union all " +
                " select '" + classification + "' as rirstClassification, " +

                " (select count(1) from(select b.id from " + tableName2 + " b  left join " + tableName1 + " a on b.id=a.customer_id where " +
                " b.customer_status='新客多次' and " +
                " b.arrival_time>='" + searchInfo.getStartTime() + "' and b.arrival_time<='" + searchInfo.getEndTime() + "' and " + condition + sqlb + " " +
                " group by b.reservations_id) t) AS outpatientVolume," +

                " (select count(1) from (select b.id from " + tableName2 + " b left join "+dept.getAbbreviation()+"deal_info a on b.id=a.customer_id where b.customer_status='新客多次' " +
                " and b.isdeal_status='已成交' and " +
                " b.arrival_time>='" + searchInfo.getStartTime() + "' and b.arrival_time<='" + searchInfo.getEndTime() + "' and " + condition+ sqlb +
                " group by b.reservations_id) t) AS volume, " +

                " ifnull((select sum(t." + performance + ") from( " +
                " select c." + performance + " from " + tableName3 + " c  " +
                " left join " + tableName1 + " a on c.deal_id=a.id " +
                " left join " + tableName2 + " b on b.id=c.customer_id " +
                " where c.recharge_time >= '" + searchInfo.getStartTime() + "' and c.recharge_time <= '" + searchInfo.getEndTime() + "'" +
                " and " + condition + sqlb + " and b.customer_status='新客多次' group by c.id) t),0.00) as " +
                " departmentPerformance, '0.00' as tcPerformance from dual " +

                " union all " +

                " select '" + classification + "' as rirstClassification, " +

                " (select count(1) from(select b.id from " + tableName2 + " b  left join " + tableName1 + " a on b.id=a.customer_id where " +
                " b.customer_status in ('新客首次','新客多次') and " +
                " b.arrival_time>='" + searchInfo.getStartTime() + "' and b.arrival_time<='" + searchInfo.getEndTime() + "' and " + condition + sqlb + " " +
                " group by b.reservations_id) t) AS outpatientVolume," +

                " (select count(1) from (select b.id from " + tableName2 + " b left join "+dept.getAbbreviation()+"deal_info a on b.id=a.customer_id where " +
                " b.customer_status in ('新客首次','新客多次') and b.isdeal_status='已成交' and " +
                " b.arrival_time>='" + searchInfo.getStartTime() + "' and b.arrival_time<='" + searchInfo.getEndTime() + "' and " + condition+ sqlb +
                " group by b.reservations_id) t) AS volume, " +

                " ifnull((select sum(t." + performance + ") from( " +
                " select c." + performance + " from " + tableName3 + " c  " +
                " left join " + tableName1 + " a on c.deal_id=a.id " +
                " left join " + tableName2 + " b on b.id=c.customer_id " +
                " where c.recharge_time >= '" + searchInfo.getStartTime() + "' and c.recharge_time <= '" + searchInfo.getEndTime() + "' and " + condition + sqlb + " " +
                " and b.customer_status in ('新客首次','新客多次') group by c.id) t),0.00) as " +
                " departmentPerformance, '0.00' as tcPerformance from dual " +

                " union all " +
                " select '" + classification + "' as rirstClassification, " +

                " (select count(1) from(select b.id from " + tableName2 + " b  left join " + tableName1 + " a on b.id=a.customer_id where " +
                " b.customer_status='老客' and " +
                " b.arrival_time>='" + searchInfo.getStartTime() + "' and b.arrival_time<='" + searchInfo.getEndTime() + "' and " + condition + sqlb + " " +
                " group by b.reservations_id) t) AS outpatientVolume," +

                " (select count(1) from (select b.id from " + tableName2 + " b left join "+dept.getAbbreviation()+"deal_info a on b.id=a.customer_id where b.customer_status='老客' " +
                " and b.isdeal_status='已成交' and " +
                " b.arrival_time>='" + searchInfo.getStartTime() + "' and b.arrival_time<='" + searchInfo.getEndTime() + "' and " + condition+ sqlb +
                " group by b.reservations_id) t) AS volume, " +

                " ifnull((select sum(t." + performance + ") from( " +
                " select c." + performance + " from " + tableName3 + " c  " +
                " left join " + tableName1 + " a on c.deal_id=a.id " +
                " left join " + tableName2 + " b on b.id=c.customer_id " +
                " where c.recharge_time >= '" + searchInfo.getStartTime() + "' and c.recharge_time <= '" + searchInfo.getEndTime() + "'" +
                " and " + condition + sqlb + " and b.customer_status='老客' group by c.id) t),0.00) as " +
                " departmentPerformance, '0.00' as tcPerformance from dual " +
                " union all " +
                " select '" + classification + "' as rirstClassification, " +

                " (select count(1) from(select b.id from " + tableName2 + " b  left join " + tableName1 + " a on b.id=a.customer_id where " +
                " b.customer_status in ('新客首次','新客多次','老客') and " +
                " b.arrival_time>='" + searchInfo.getStartTime() + "' and b.arrival_time<='" + searchInfo.getEndTime() + "' and " + condition + sqlb + " " +
                " group by b.reservations_id) t) AS outpatientVolume," +

                " (select count(1) from (select b.id from " + tableName2 + " b left join "+dept.getAbbreviation()+"deal_info a on b.id=a.customer_id where " +
                " b.customer_status in ('新客首次','新客多次','老客') and b.isdeal_status='已成交' and " +
                " b.arrival_time>='" + searchInfo.getStartTime() + "' and b.arrival_time<='" + searchInfo.getEndTime() + "' and " + condition+ sqlb +
                " group by b.reservations_id) t) AS volume, " +

                " ifnull((select sum(t." + performance + ") from( " +
                " select c." + performance + "  from " + tableName3 + " c  " +
                " left join " + tableName1 + " a on c.deal_id=a.id " +
                " left join " + tableName2 + " b on b.id=c.customer_id " +
                " where c.recharge_time >= '" + searchInfo.getStartTime() + "' and c.recharge_time <= '" + searchInfo.getEndTime() + "' " +
                " and " + condition + sqlb + " and b.customer_status in ('新客首次','新客多次','老客') group by c.id) t),0.00)  as " +
                " departmentPerformance,'0.00' as tcPerformance from dual ";
        if (i == list1.size() - 1) {
        } else {
            sql += " union all ";
        }
        return sql;
    }

    public String getsqlDepartmentcz(SearchWholeHospitalInfo searchInfo, String classification, String condition, int i, List<SearchWholeHospitalInfo> list1) {
        String getabbreviationsql = " select id,abbreviation,simplename from dept t where t.simplename='" + searchInfo.getArrivalStore() + "'";
        Dept dept = sqlMapper.selectOne(getabbreviationsql, Dept.class);
        String tableHead = "";
        if (dept == null) {
            tableHead = ShiroKit.getUser().getAbbreviation();
        } else {
            tableHead = dept.getAbbreviation();
        }
        String tableName1 = tableHead + "deal_info";
        String tableName2 = tableHead + "customer_info";
        String tableName3 = tableHead + "recharge_info";
        String sqlb = " ";
        String performance = "hospital_performance";
        if (StringUtil.isNotEmpty(searchInfo.getDimensionSelectionCustomerAll())) {
            if ("ks".equals(searchInfo.getDimensionSelectionCustomerAll())) {
                sqlb += "";
            } else {
                sqlb += " and b.treatment_unit='" + searchInfo.getDimensionSelectionCustomerAll() + "'";
            }
            performance = " section_performance";
        }
        String sql = " select '" + classification + "' as rirstClassification, " +

                " (select count(1) from(select b.id from " + tableName2 + " b  left join " + tableName1 + " a on b.id=a.customer_id where " +
                " b.customer_status='新客首次' and " +
                " b.arrival_time>='" + searchInfo.getStartTime() + "' and b.arrival_time<='" + searchInfo.getEndTime() + "' and " + condition + sqlb + " " +
                " group by b.reservations_id) t) AS outpatientVolume," +

                " (select count(1) from (select b.id from " + tableName2 + " b left join "+dept.getAbbreviation()+"deal_info a on b.id=a.customer_id where b.customer_status='新客首次' " +
                " and b.isdeal_status='已成交' and " +
                " b.arrival_time>='" + searchInfo.getStartTime() + "' and b.arrival_time<='" + searchInfo.getEndTime() + "' and " + condition+ sqlb +
                " group by b.reservations_id) t) AS volume, " +

                " ifnull((select sum(t." + performance + ") from( " +
                " select c." + performance + " from " + tableName3 + " c  " +
                " left join " + tableName1 + " a on c.deal_id=a.id " +
                " left join " + tableName2 + " b on b.id=c.customer_id " +
                " where c.recharge_time >= '" + searchInfo.getStartTime() + "' and c.recharge_time <= '" + searchInfo.getEndTime() + "'" +
                " and " + condition + sqlb + " and b.customer_status='新客首次' group by c.id) t),0.00)  as " +
                " departmentPerformance, '0.00' as tcPerformance from dual " +

                " union all " +
                " select '" + classification + "' as rirstClassification, " +

                " (select count(1) from(select b.id from " + tableName2 + " b  left join " + tableName1 + " a on b.id=a.customer_id where " +
                " b.customer_status='新客多次' and " +
                " b.arrival_time>='" + searchInfo.getStartTime() + "' and b.arrival_time<='" + searchInfo.getEndTime() + "' and " + condition + sqlb + " " +
                " group by b.reservations_id) t) AS outpatientVolume," +

                " (select count(1) from (select b.id from " + tableName2 + " b left join "+dept.getAbbreviation()+"deal_info a on b.id=a.customer_id where b.customer_status='新客多次' " +
                " and b.isdeal_status='已成交' and " +
                " b.arrival_time>='" + searchInfo.getStartTime() + "' and b.arrival_time<='" + searchInfo.getEndTime() + "' and " + condition+ sqlb +
                " group by b.reservations_id) t) AS volume, " +

                " ifnull((select sum(t." + performance + ") from( " +
                " select c." + performance + " from " + tableName3 + " c  " +
                " left join " + tableName1 + " a on c.deal_id=a.id " +
                " left join " + tableName2 + " b on b.id=c.customer_id " +
                " where c.recharge_time >= '" + searchInfo.getStartTime() + "' and c.recharge_time <= '" + searchInfo.getEndTime() + "'" +
                " and " + condition + sqlb + " and b.customer_status='新客多次' group by c.id) t),0.00) as " +
                " departmentPerformance, '0.00' as tcPerformance from dual " +

                " union all " +

                " select '" + classification + "' as rirstClassification, " +

                " (select count(1) from(select b.id from " + tableName2 + " b  left join " + tableName1 + " a on b.id=a.customer_id where " +
                " b.customer_status in ('新客首次','新客多次') and " +
                " b.arrival_time>='" + searchInfo.getStartTime() + "' and b.arrival_time<='" + searchInfo.getEndTime() + "' and " + condition + sqlb + " " +
                " group by b.reservations_id) t) AS outpatientVolume," +

                " (select count(1) from (select b.id from " + tableName2 + " b left join "+dept.getAbbreviation()+"deal_info a on b.id=a.customer_id where " +
                " b.customer_status in ('新客首次','新客多次') and b.isdeal_status='已成交' and " +
                " b.arrival_time>='" + searchInfo.getStartTime() + "' and b.arrival_time<='" + searchInfo.getEndTime() + "' and " + condition+ sqlb +
                " group by b.reservations_id) t) AS volume, " +

                " ifnull((select sum(t." + performance + ") from( " +
                " select c." + performance + " from " + tableName3 + " c  " +
                " left join " + tableName1 + " a on c.deal_id=a.id " +
                " left join " + tableName2 + " b on b.id=c.customer_id " +
                " where c.recharge_time >= '" + searchInfo.getStartTime() + "' and c.recharge_time <= '" + searchInfo.getEndTime() + "' and " + condition + sqlb + " " +
                " and b.customer_status in ('新客首次','新客多次') group by c.id) t),0.00) as " +
                " departmentPerformance, '0.00' as tcPerformance from dual " +

                " union all " +
                " select '" + classification + "' as rirstClassification, " +

                " (select count(1) from(select b.id from " + tableName2 + " b  left join " + tableName1 + " a on b.id=a.customer_id where " +
                " b.customer_status='老客' and " +
                " b.arrival_time>='" + searchInfo.getStartTime() + "' and b.arrival_time<='" + searchInfo.getEndTime() + "' and " + condition + sqlb + " " +
                " group by b.reservations_id) t) AS outpatientVolume," +

                " (select count(1) from (select b.id from " + tableName2 + " b left join "+dept.getAbbreviation()+"deal_info a on b.id=a.customer_id where b.customer_status='老客' " +
                " and b.isdeal_status='已成交' and " +
                " b.arrival_time>='" + searchInfo.getStartTime() + "' and b.arrival_time<='" + searchInfo.getEndTime() + "' and " + condition+ sqlb +
                " group by b.reservations_id) t) AS volume, " +

                " ifnull((select sum(t." + performance + ") from( " +
                " select c." + performance + " from " + tableName3 + " c  " +
                " left join " + tableName1 + " a on c.deal_id=a.id " +
                " left join " + tableName2 + " b on b.id=c.customer_id " +
                " where c.recharge_time >= '" + searchInfo.getStartTime() + "' and c.recharge_time <= '" + searchInfo.getEndTime() + "'" +
                " and " + condition + sqlb + " and b.customer_status='老客' group by c.id) t),0.00) as " +
                " departmentPerformance, '0.00' as tcPerformance from dual " +
                " union all " +
                " select '" + classification + "' as rirstClassification, " +

                " (select count(1) from(select b.id from " + tableName2 + " b  left join " + tableName1 + " a on b.id=a.customer_id where " +
                " b.customer_status in ('新客首次','新客多次','老客') and " +
                " b.arrival_time>='" + searchInfo.getStartTime() + "' and b.arrival_time<='" + searchInfo.getEndTime() + "' and " + condition + sqlb + " " +
                " group by b.reservations_id) t) AS outpatientVolume," +

                " (select count(1) from (select b.id from " + tableName2 + " b left join "+dept.getAbbreviation()+"deal_info a on b.id=a.customer_id where " +
                " b.customer_status in ('新客首次','新客多次','老客') and b.isdeal_status='已成交' and " +
                " b.arrival_time>='" + searchInfo.getStartTime() + "' and b.arrival_time<='" + searchInfo.getEndTime() + "' and " + condition+ sqlb +
                " group by b.reservations_id) t) AS volume, " +

                " ifnull((select sum(t." + performance + ") from( " +
                " select c." + performance + "  from " + tableName3 + " c  " +
                " left join " + tableName1 + " a on c.deal_id=a.id " +
                " left join " + tableName2 + " b on b.id=c.customer_id " +
                " where c.recharge_time >= '" + searchInfo.getStartTime() + "' and c.recharge_time <= '" + searchInfo.getEndTime() + "' " +
                " and " + condition + sqlb + " and b.customer_status in ('新客首次','新客多次','老客') group by c.id) t),0.00)  as " +
                " departmentPerformance,'0.00' as tcPerformance from dual ";
        if (i == list1.size() - 1) {
        } else {
            sql += " union all ";
        }
        return sql;
    }

    public String getTotleSqlDepartment(SearchWholeHospitalInfo searchInfo) {
        String getabbreviationsql = " select id,abbreviation,simplename from dept t where t.simplename='" + searchInfo.getArrivalStore() + "'";
        Dept dept = sqlMapper.selectOne(getabbreviationsql, Dept.class);
        String tableHead = "";
        if (dept == null) {
            tableHead = ShiroKit.getUser().getAbbreviation();
        } else {
            tableHead = dept.getAbbreviation();
        }
        String tableName2 = tableHead + "customer_info";
        String tableName3 = tableHead + "recharge_info";
        String sqlb = " ";
        String performance = "hospital_performance";
        if (StringUtil.isNotEmpty(searchInfo.getDimensionSelectionCustomerAll())) {
            if ("ks".equals(searchInfo.getDimensionSelectionCustomerAll())) {
                sqlb += "";
            } else {
                sqlb += " and b.treatment_unit='" + searchInfo.getDimensionSelectionCustomerAll() + "'";
            }
            performance = " section_performance";
        }
        String sql = " select '合计' as rirstClassification, " +

                " (select count(1) from (select b.id from " + tableName2 + " b where b.customer_status='新客首次' and " +
                " b.arrival_time>='" + searchInfo.getStartTime() + "' and b.arrival_time<='" + searchInfo.getEndTime() + "' " + sqlb +
                " group by b.reservations_id) t) AS outpatientVolume," +

                " (select count(1) from (select b.id from " + tableName2 + " b where b.customer_status='新客首次' and b.isdeal_status='已成交' and " +
                " b.arrival_time>='" + searchInfo.getStartTime() + "' and b.arrival_time<='" + searchInfo.getEndTime() + "' " + sqlb +
                " group by b.reservations_id) t) AS volume, " +

                " ifnull((select sum(t." + performance + ") from( " +
                " select c." + performance + " from " + tableName3 + " c  " +
                " left join " + tableName2 + " b on b.id=c.customer_id " +
                " where c.recharge_time >= '" + searchInfo.getStartTime() + "' and c.recharge_time <= '" + searchInfo.getEndTime() + "' " +
                " " + sqlb + " and b.customer_status='新客首次' group by c.id) t),0.00) as departmentPerformance," +

                " ifnull((select sum(t." + performance + ") from( " +
                " select c." + performance + " from " + tableName3 + " c  " +
                " left join " + tableName2 + " b on b.id=c.customer_id " +
                " where b.arrival_time >= '" + searchInfo.getStartTime() + "' and b.arrival_time <= '" + searchInfo.getEndTime() + "'" +
                " " + sqlb + " and b.customer_status='新客首次' and c.tc_name is not null group by c.id) t),0.00) as tcPerformance from dual " +

                " union all " +
                " select '合计' as rirstClassification, " +

                " (select count(1) from (select b.id from " + tableName2 + " b where b.customer_status='新客多次' and " +
                " b.arrival_time>='" + searchInfo.getStartTime() + "' and b.arrival_time<='" + searchInfo.getEndTime() + "' " + sqlb +
                " group by b.reservations_id) t) AS outpatientVolume, " +

                " (select count(1) from (select b.id from " + tableName2 + " b where b.customer_status='新客多次' and b.isdeal_status='已成交' and " +
                " b.arrival_time>='" + searchInfo.getStartTime() + "' and b.arrival_time<='" + searchInfo.getEndTime() + "' " + sqlb +
                " group by b.reservations_id) t) AS volume, " +

                " ifnull((select sum(t." + performance + ") from( " +
                " select c." + performance + " from " + tableName3 + " c  " +
                " left join " + tableName2 + " b on b.id=c.customer_id " +
                " where c.recharge_time >= '" + searchInfo.getStartTime() + "' and c.recharge_time <= '" + searchInfo.getEndTime() + "' " +
                " " + sqlb + " and b.customer_status='新客多次' group by c.id) t),0.00) as departmentPerformance," +

                " ifnull((select sum(t." + performance + ") from( " +
                " select c." + performance + " from " + tableName3 + " c  " +
                " left join " + tableName2 + " b on b.id=c.customer_id " +
                " where b.arrival_time >= '" + searchInfo.getStartTime() + "' and b.arrival_time <= '" + searchInfo.getEndTime() + "'" +
                " " + sqlb + " and b.customer_status='新客多次' and c.tc_name is not null group by c.id) t),0.00) as tcPerformance from dual " +

                " union all " +
                " select '合计' as rirstClassification, " +
                " (select count(1) from (select b.id from " + tableName2 + " b where b.customer_status in ('新客首次','新客多次') and " +
                " b.arrival_time>='" + searchInfo.getStartTime() + "' and b.arrival_time<='" + searchInfo.getEndTime() + "' " + sqlb +
                " group by b.reservations_id) t) AS outpatientVolume," +

                " (select count(1) from (select b.id from " + tableName2 + " b where b.customer_status in ('新客首次','新客多次') and b.isdeal_status='已成交' and " +
                " b.arrival_time>= '" + searchInfo.getStartTime() + "' " +
                " and b.arrival_time<='" + searchInfo.getEndTime() + "' group by b.reservations_id) t) AS volume, " +

                " ifnull((select sum(t." + performance + ") from( " +
                " select c." + performance + " from " + tableName3 + " c  " +
                " left join " + tableName2 + " b on b.id=c.customer_id " +
                " where c.recharge_time >= '" + searchInfo.getStartTime() + "' and c.recharge_time <= '" + searchInfo.getEndTime() + "' " +
                " " + sqlb + " and b.customer_status in ('新客首次','新客多次') group by c.id) t),0.00) as departmentPerformance," +

                " ifnull((select sum(t." + performance + ") from( " +
                " select c." + performance + " from " + tableName3 + " c  " +
                " left join " + tableName2 + " b on b.id=c.customer_id " +
                " where b.arrival_time >= '" + searchInfo.getStartTime() + "' and b.arrival_time <= '" + searchInfo.getEndTime() + "'" +
                " " + sqlb + " and b.customer_status in ('新客首次','新客多次') and c.tc_name is not null group by c.id) t) ,0.00) as tcPerformance from dual " +

                " union all " +
                " select '合计' as rirstClassification, " +
                " (select count(1) from (select b.id from " + tableName2 + " b where b.customer_status ='老客' and " +
                " b.arrival_time>='" + searchInfo.getStartTime() + "' and b.arrival_time<='" + searchInfo.getEndTime() + "' " + sqlb +
                " group by b.reservations_id) t) AS outpatientVolume," +

                " (select count(1) from (select b.id from " + tableName2 + " b where b.customer_status='老客' and b.isdeal_status='已成交' and " +
                " b.arrival_time>='" + searchInfo.getStartTime() + "' and b.arrival_time<='" + searchInfo.getEndTime() + "' " + sqlb +
                " group by b.reservations_id) t) AS volume, " +

                " ifnull((select sum(t." + performance + ") from( " +
                " select c." + performance + " from " + tableName3 + " c  " +
                " left join " + tableName2 + " b on b.id=c.customer_id " +
                " where c.recharge_time >= '" + searchInfo.getStartTime() + "' and c.recharge_time <= '" + searchInfo.getEndTime() + "' " +
                " " + sqlb + " and b.customer_status ='老客' group by c.id) t),0.00) as departmentPerformance, " +

                " ifnull((select sum(t." + performance + ") from( " +
                " select c." + performance + " from " + tableName3 + " c  " +
                " left join " + tableName2 + " b on b.id=c.customer_id " +
                " where b.arrival_time >= '" + searchInfo.getStartTime() + "' and b.arrival_time <= '" + searchInfo.getEndTime() + "'" +
                " " + sqlb + " and b.customer_status='老客' and c.tc_name is not null group by c.id) t),0.00) as tcPerformance from dual " +

                " union all " +
                " select '合计' as rirstClassification, " +

                " (select count(1) from (select b.id from " + tableName2 + " b where " +
                " b.customer_status in ('新客首次','新客多次','老客') and b.arrival_time>='" + searchInfo.getStartTime() + "' and " +
                " b.arrival_time<='" + searchInfo.getEndTime() + "' " + sqlb + " group by b.reservations_id) t) AS outpatientVolume," +

                " (select count(1) from (select b.id from " + tableName2 + " b where b.customer_status in ('新客首次','新客多次','老客') and b.isdeal_status='已成交' and " +
                " b.arrival_time>= '" + searchInfo.getStartTime() + "' " +
                " and b.arrival_time<='" + searchInfo.getEndTime() + "' group by b.reservations_id) t) AS volume, " +

                " ifnull((select sum(t." + performance + ") from( " +
                " select c." + performance + " from " + tableName3 + " c  " +
                " left join " + tableName2 + " b on b.id=c.customer_id " +
                " where c.recharge_time >= '" + searchInfo.getStartTime() + "' and c.recharge_time <= '" + searchInfo.getEndTime() + "' " +
                " " + sqlb + " and b.customer_status in ('新客首次','新客多次','老客') group by c.id) t),0.00) as departmentPerformance," +

                " ifnull((select sum(t." + performance + ") from( " +
                " select c." + performance + " from " + tableName3 + " c  " +
                " left join " + tableName2 + " b on b.id=c.customer_id  " +
                " where c.recharge_time >= '" + searchInfo.getStartTime() + "' and c.recharge_time <= '" + searchInfo.getEndTime() + "'" +
                " " + sqlb + " and b.customer_status in ('新客首次','新客多次','老客') and c.tc_name is not null group by c.id) t),0.00) as tcPerformance from dual ";
        return sql;
    }
}